﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using System.Web.Configuration;
using System.Text.RegularExpressions;

using Recon.Portal.DataObjects;

namespace Recon.Portal.Business
{
    /// <summary>
    /// Summary description for cls_UploadFile
    /// </summary>
    public class UploadHelper
    {
        //Initialize mysql connection
        string autoFileId;
        int rowCount = 0;
        SqlConnection connection;// = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
        SqlCommand cmd;
        User objUserVO;

        public UploadHelper()
        {
            //
            // TODO: Add constructor logic here
            connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["SqlConnection"].ToString());
            //       
        }

        private void InsertIntoFileUpload(string fileType, string strFilename, string strFileExt)
        {

            string status = "N";//while uploading new always N
            autoFileId = System.DateTime.Now.ToString("ddMMyyHHmmss");
            connection.Open();
            string strQuery = "insert into pro_file_upload_details(customer_id,file_id,file_name, file_type, file_upload_date, file_upload_by," +
                "file_status, error_desc) values " +
                "('" + objUserVO.CustomerID + "','" + autoFileId + "','" + strFilename + "','" + fileType + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "','" + objUserVO.UserID + "','" + status + "','null')";
            cmd = new SqlCommand(strQuery, connection);
            int retval = cmd.ExecuteNonQuery();
            //If it is -1 show appropriate message

        }

        private int IsFileAlreadyUploaded(string fileType, string strFilename)
        {
            int fileExistOrNot = 0;
            string strQuery = "";
            connection.Open();
            strQuery = "select count(1) from pro_file_upload_details where customer_id='" + objUserVO.CustomerID + "' and file_type = '" + fileType + "' and file_name = '" + strFilename + "' and file_status = 'UPLOD'";
            cmd = new SqlCommand(strQuery, connection);
            fileExistOrNot = Convert.ToInt32(cmd.ExecuteScalar());
            connection.Close();
            return fileExistOrNot;
        }
        public int ParseUploadedFile(User currentUser, string fileType, string strFilename, string strFileExt, string filePath,string bankId)
        {
            int retval;
            int isExist = 0;
            objUserVO = currentUser;
            isExist = IsFileAlreadyUploaded(fileType, strFilename);
            if (isExist == 0)
            {
                InsertIntoFileUpload(fileType, strFilename, strFileExt);
                DataTable dtStaggingData = InsertIntoStagging(filePath);
                //***********************For Validation**********************************
                //retval = ValidateFileRecords(fileType, dtStaggingData, bankId);
                //***********************Validation End**********************************
                retval = InsertIntoInvoiceStatement(fileType, dtStaggingData);
            }
            else
            {
                retval = 2;
            }
            return retval;
        }

        private DataTable InsertIntoStagging(string filePath)
        {
            char strDelimiter = ',';
            DataSet dsReadFile = new DataSet();
            DataTable dtReadFile = new DataTable();

            List<List<String>> rowsList = new List<List<String>>();
            List<String> columnsList = new List<String>();

            using (StreamReader reader = File.OpenText(filePath))
            {
                string line;
                int cnt = 1;
                //  Response.Write("Start at execution" + System.DateTime.Now);
                while ((line = reader.ReadLine()) != null)
                {
                    //aaaaaaaaaaaaa,1111111111,bbbbbbbbbbbbbbb,2222222222222222,cccccccccccccc,333333333333333333,dddddddddddd,444444444444                                
                    //////columnsList = line.Split(strDelimiter).ToList();
                    //////columnsList.Add(Convert.ToString(fileId));

                    columnsList = line.Split(strDelimiter).ToList();
                    columnsList.Insert(0, (Convert.ToString(autoFileId)));
                    //columnsList.Insert(1,  (Convert.ToString(cnt)));

                    //******************Commented For Error Message Add
                    //columnsList.Add(Convert.ToString(cnt));
                    //******************Commented For Error Message Add
                    //columnsList.Count

                    if (dtReadFile.Columns.Count <= 0)
                    {
                        //columnsList.Count();                                   
                        foreach (String str in columnsList)
                        {
                            dtReadFile.Columns.Add();

                        }
                        //dtReadFile.Columns.Add("FileId");
                    }
                    //Add row value from list to table row                               
                    dtReadFile.Rows.Add(columnsList.ToArray());
                    cnt++;
                }
                dsReadFile.Tables.Add(dtReadFile);
                //To add all column of database  to datatable
                while (dtReadFile.Columns.Count != 101)
                {
                    dtReadFile.Columns.Add();
                }
                //Take count of newly created file id record in stagging table
                SqlDataAdapter adp = new SqlDataAdapter("select * from test_pro_file_staging where column1 = '" + Convert.ToString(autoFileId) + "'", connection);
                DataTable dt = new DataTable();
                adp.Fill(dt);

                // If No records found in the table for this new file id then insert all the rows of the dtReadFile into "pro_file_staging" table
                SqlCommandBuilder cmdBuilder;
                if (dt != null)
                {
                    if (dt.Rows.Count == 0)
                    {
                        // Update the DataSet with the Database
                        cmdBuilder = new SqlCommandBuilder(adp);
                        adp.Update(dtReadFile);//Update database table with file record.

                    }
                }
            }
            return dtReadFile;


        }

        private int InsertIntoInvoiceStatement(string fileType, DataTable dtStaggingData)
        {
            //Below code for fetch data from conf_customer_file_format
            try
            {
                string strQuery = "";
                //Get field mapping id from the database
                strQuery = "select field_mapping_id from conf_customer_file_format where customer_id='" + objUserVO.CustomerID + "' and file_type = '" + fileType + "'";
                cmd = new SqlCommand(strQuery, connection);
                SqlDataReader drField_mapping_id = cmd.ExecuteReader();
                string field_mapping_id = null;

                //Loop through all field mapping id to retrieve source and target index
                while (drField_mapping_id.Read())
                {

                    field_mapping_id = field_mapping_id + "'" + drField_mapping_id[0].ToString() + "',";
                }
                drField_mapping_id.Close();
                drField_mapping_id.Dispose();
                field_mapping_id = field_mapping_id.Remove(field_mapping_id.Length - 1, 1);

                //Below code for fetch data from conf_file_field_mapping
                strQuery = "";
                cmd = null;
                strQuery = "select Source_Field_Index,Target_Field_Index from conf_file_field_mapping where field_mapping_id in ( " + field_mapping_id + ")";
                cmd = new SqlCommand(strQuery, connection);
                SqlDataReader drSourceTarget = cmd.ExecuteReader();

                DataTable dtField_mapping_id = new DataTable();
                dtField_mapping_id.Load(drSourceTarget);

                String schemaString = "";
                DataTable dtTemp;
                if (fileType == "I")
                {
                    //Get datatable for Invoice data table structure
                    dtTemp = getInvoiceDataTable();
                    schemaString = "SELECT * FROM pro_invoice where invoice_unique_id = 0";
                }
                else
                {
                    //Get datatable for Statement data table structure
                    dtTemp = getStatementDataTable();
                    schemaString = "SELECT * FROM pro_statement where statement_unique_id = 0";
                }

                //Check for source target index datatable
                if (dtField_mapping_id != null && dtField_mapping_id.Rows.Count > 0)
                {
                    //Loop through each stagging row and use inner loop to compare source field and target field
                    for (int iRow = 0; iRow < dtStaggingData.Rows.Count; iRow++)
                    {
                        DataRow dr = dtTemp.NewRow();
                        for (int i = 0; i < dtField_mapping_id.Rows.Count; i++)
                        {
                            int sourceIndx = Convert.ToInt32(dtField_mapping_id.Rows[i]["Source_Field_Index"]);
                            int targetIndx = Convert.ToInt32(dtField_mapping_id.Rows[i]["Target_Field_Index"]);
                            dr[targetIndx - 1] = dtStaggingData.Rows[iRow]["Column" + sourceIndx];

                        }
                        //Add newly mapped row into Statement/Invoice datatable.
                        dtTemp.Rows.Add(dr);
                    }
                }

                SqlDataAdapter adpInvoiceStatement = new SqlDataAdapter(schemaString, connection);
                SqlCommandBuilder cmdBuilder;
                adpInvoiceStatement.UpdateBatchSize = 10;

                if (dtTemp != null)
                {
                    if (dtTemp.Rows.Count != 0)
                    {
                        // Update the DataSet with the Database
                        cmdBuilder = new SqlCommandBuilder(adpInvoiceStatement);
                        adpInvoiceStatement.Update(dtTemp);//Update database table with file record.
                        rowCount = dtTemp.Rows.Count;
                    }
                }

                if (rowCount > 0)
                {
                    string strQueryInvoice = "update pro_file_upload_details set file_status = 'UPLOD',row_count = '" + rowCount + "' where customer_id = '" + objUserVO.CustomerID + "' and file_id = '" + Convert.ToString(autoFileId) + "' and file_type = '" + fileType + "'";
                    cmd = new SqlCommand(strQueryInvoice, connection);
                    int retval = cmd.ExecuteNonQuery();
                }
                return rowCount;

            }
            catch (Exception ex)
            {
                return rowCount;
                throw ex;
            }
            finally
            {

            }
        }

        public static User getTestUser()
        {
            User user = new User();
            user.UserID = "user1";
            user.CustomerID = "001";
            return user;
        }

        public static DataTable getInvoiceDataTable()
        {
            DataTable dtStaggingToInvoice = new DataTable();
            dtStaggingToInvoice.Columns.Add("invoice_unique_id", typeof(System.Decimal));
            dtStaggingToInvoice.Columns.Add("remitter_id", typeof(System.String));
            dtStaggingToInvoice.Columns.Add("customer_id", typeof(System.String));
            dtStaggingToInvoice.Columns.Add("invoice_number", typeof(System.String));
            dtStaggingToInvoice.Columns.Add("date", typeof(System.DateTime));
            dtStaggingToInvoice.Columns.Add("invoice_due_date", typeof(System.DateTime));
            //dtStaggingToInvoice.Columns.Add("currency", typeof(System.String));//Not in new table structure
           
            dtStaggingToInvoice.Columns.Add("po_ref", typeof(System.String));
            dtStaggingToInvoice.Columns.Add("items", typeof(System.Int32));
            dtStaggingToInvoice.Columns.Add("amount", typeof(System.Double));
            //Invoice Due Date -datetime  //Items - int  are the two new fields
            //dtStaggingToInvoice.Columns.Add("service_tax", typeof(System.Int32));//Not in new table structure
            //dtStaggingToInvoice.Columns.Add("vat", typeof(System.Int32));//Not in new table structure
            //dtStaggingToInvoice.Columns.Add("cess", typeof(System.Int32));//Not in new table structure
            //dtStaggingToInvoice.Columns.Add("sales_tax", typeof(System.Int32));//Not in new table structure
            //dtStaggingToInvoice.Columns.Add("tax1", typeof(System.Int32));//Not in new table structure
            //dtStaggingToInvoice.Columns.Add("tax2", typeof(System.Int32));//Not in new table structure
            //dtStaggingToInvoice.Columns.Add("tax3", typeof(System.Int32));//Not in new table structure
            //dtStaggingToInvoice.Columns.Add("discount", typeof(System.Int32));//Not in new table structure
            dtStaggingToInvoice.Columns.Add("invoic_status", typeof(System.Char));
            dtStaggingToInvoice.Columns.Add("is_partial", typeof(System.Boolean));
            dtStaggingToInvoice.Columns.Add("reconciled_amount", typeof(System.Double));
            
            
            return dtStaggingToInvoice;
        }

        public static DataTable getStatementDataTable()
        {
            DataTable dtStaggingToStatement = new DataTable();
            dtStaggingToStatement.Columns.Add("statement_unique_id", typeof(System.Decimal));
            dtStaggingToStatement.Columns.Add("customer_id", typeof(System.String));
            dtStaggingToStatement.Columns.Add("bank_id", typeof(System.String));
            dtStaggingToStatement.Columns.Add("statement_date", typeof(System.DateTime));
            dtStaggingToStatement.Columns.Add("posting_date", typeof(System.DateTime));
            dtStaggingToStatement.Columns.Add("value_date", typeof(System.DateTime));
            dtStaggingToStatement.Columns.Add("tran_type", typeof(System.String));
            dtStaggingToStatement.Columns.Add("narration1", typeof(System.String));
            dtStaggingToStatement.Columns.Add("narration2", typeof(System.String));
            //dtStaggingToStatement.Columns.Add("narration3", typeof(System.String));//Not in new table structure
            //dtStaggingToStatement.Columns.Add("narration4", typeof(System.String));//Not in new table structure
            dtStaggingToStatement.Columns.Add("ref_no_or_cheque_no", typeof(System.String));
            //Amount column is required, can not process correctly without Amount column
            dtStaggingToStatement.Columns.Add("amount", typeof(System.Double));
            dtStaggingToStatement.Columns.Add("statement_status", typeof(System.Char));
            dtStaggingToStatement.Columns.Add("is_partial", typeof(System.Boolean));
            dtStaggingToStatement.Columns.Add("account_no", typeof(System.String));
            dtStaggingToStatement.Columns.Add("reconciled_amount", typeof(System.Double));
            return dtStaggingToStatement;
        }

        public List<FileFormat> getFileTypeByCustomer(String customerId,String fileType,String BankId)
        {
            //field_mapping_id,file_type,file_format,separator,bank_id
            String sqlQuery = "select field_mapping_id,file_format,separator from conf_customer_file_format where " +
                             (BankId != "0" ? " bank_id = '" + BankId + "' and " : "") +
                             " customer_id = '" + customerId + "' and file_type = '" + fileType +"'" ;
             
            List<FileFormat> bankLst = null;
            try
            {
                connection.Open();

                SqlCommand cmd = new SqlCommand(sqlQuery, connection);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    int columnIndx = 0;
                    bankLst = new List<FileFormat>();

                    while (reader.Read())
                    {
                        columnIndx = 0;
                        FileFormat bank = new FileFormat();
                        bank.FieldMappingId = reader.GetString(columnIndx++);
                        bank.FileFormats = reader.GetString(columnIndx++);
                        bank.Seperator = reader.GetString(columnIndx++);
                        bankLst.Add(bank);
                    }
                }
            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                connection.Close();
            }

            return bankLst;
        }

        //private DataTable InsertIntoStagging(string filePath)
        //{
        //    char strDelimiter = ',';
        //    DataSet dsReadFile = new DataSet();
        //    DataTable dtReadFile = new DataTable();

        //    List<List<String>> rowsList = new List<List<String>>();
        //    List<String> columnsList = new List<String>();

        //    using (StreamReader reader = File.OpenText(filePath))
        //    {
        //        string line;
        //        int cnt = 0;
        //        //  Response.Write("Start at execution" + System.DateTime.Now);
        //        while ((line = reader.ReadLine()) != null)
        //        {
        //            //aaaaaaaaaaaaa,1111111111,bbbbbbbbbbbbbbb,2222222222222222,cccccccccccccc,333333333333333333,dddddddddddd,444444444444                                
        //            //////columnsList = line.Split(strDelimiter).ToList();
        //            //////columnsList.Add(Convert.ToString(fileId));

        //            columnsList = line.Split(strDelimiter).ToList();
        //            columnsList.Insert(0, (Convert.ToString(autoFileId)));

        //            if (dtReadFile.Columns.Count <= 0)
        //            {
        //                //columnsList.Count();                                   
        //                foreach (String str in columnsList)
        //                {
        //                    dtReadFile.Columns.Add();

        //                }
        //                //dtReadFile.Columns.Add("FileId");
        //            }
        //            //Add row value from list to table row                               
        //            dtReadFile.Rows.Add(columnsList.ToArray());
        //            cnt++;
        //        }
        //        dsReadFile.Tables.Add(dtReadFile);
        //        //To add all column of database  to datatable
        //        while (dtReadFile.Columns.Count != 101)
        //        {
        //            dtReadFile.Columns.Add();
        //        }
        //        //Take count of newly created file id record in stagging table
        //        SqlDataAdapter adp = new SqlDataAdapter("select * from test_pro_file_staging where column1 = '" + Convert.ToString(autoFileId) + "'", connection);
        //        DataTable dt = new DataTable();
        //        adp.Fill(dt);

        //        // If No records found in the table for this new file id then insert all the rows of the dtReadFile into "pro_file_staging" table
        //        SqlCommandBuilder cmdBuilder;
        //        if (dt != null)
        //        {
        //            if (dt.Rows.Count == 0)
        //            {
        //                // Update the DataSet with the Database
        //                cmdBuilder = new SqlCommandBuilder(adp);
        //                adp.Update(dtReadFile);//Update database table with file record.

        //            }
        //        }
        //    }
        //    return dtReadFile;


        //}

        private int ValidateFileRecords(string fileType, DataTable dtStaggingData,string bankId)
        {
            //Below code for fetch data from conf_customer_file_format
            try
            {
                string strQuery = "";
                if (fileType == "S")
                {
                    strQuery = "select field_mapping_id from conf_customer_file_format where customer_id='" + objUserVO.CustomerID + "' and file_type = '" + fileType + "' and bank_id ='" + bankId + "'";
                }
                else if (fileType == "I")
                {
                    strQuery = "select field_mapping_id from conf_customer_file_format where customer_id='" + objUserVO.CustomerID + "' and file_type = '" + fileType + "'";
                }
                cmd = new SqlCommand(strQuery, connection);
                SqlDataReader drField_mapping_id = cmd.ExecuteReader();
                string field_mapping_id = null;

                while (drField_mapping_id.Read())
                {

                    field_mapping_id = field_mapping_id + "'" + drField_mapping_id[0].ToString() + "',";
                }
                drField_mapping_id.Close();
                drField_mapping_id.Dispose();
                field_mapping_id = field_mapping_id.Remove(field_mapping_id.Length - 1, 1);

                //Below code for fetch data from conf_file_field_mapping
                strQuery = "";
                cmd = null;
                strQuery = "select Source_Field_Index,Target_Field_Index from conf_file_field_mapping where field_mapping_id in ( " + field_mapping_id + ")";
                cmd = new SqlCommand(strQuery, connection);
                SqlDataReader drSourceTarget = cmd.ExecuteReader();

                DataTable dtField_mapping_id = new DataTable();
                dtField_mapping_id.Load(drSourceTarget);

                String schemaString = "";
                DataTable dtTemp;
                if (fileType == "S")
                {
                    //dtTemp = getInvoiceDataTable();
                    //schemaString = "SELECT * FROM pro_invoice where invoice_unique_id = 0";
                    string errorColumn = string.Empty;
                    if (dtField_mapping_id != null && dtField_mapping_id.Rows.Count > 0)
                    {
                        for (int iRow = 0; iRow < dtStaggingData.Rows.Count; iRow++)
                        {
                            //DataRow dr = dtTemp.NewRow();
                            for (int i = 0; i < dtField_mapping_id.Rows.Count; i++)
                            {
                                int sourceIndx = Convert.ToInt32(dtField_mapping_id.Rows[i]["Source_Field_Index"]);
                                int targetIndx = Convert.ToInt32(dtField_mapping_id.Rows[i]["Target_Field_Index"]);
                                string targetColumnName = Convert.ToString(dtField_mapping_id.Rows[i]["Target_Column_Name"]);
                                int targetColumnLength = Convert.ToInt32(dtField_mapping_id.Rows[i]["Target_Column_Length"]);
                                string targetColumnValidation = Convert.ToString(dtField_mapping_id.Rows[i]["Target_Column_Validation"]);
                                bool IsMandatory = Convert.ToBoolean(dtField_mapping_id.Rows[i]["IsMandatory"]);
                                //string targetColumnName = Convert.ToString(dtField_mapping_id.Rows[i]["Target_Column_Name"]);
                                if (IsMandatory)
                                {
                                    if (!string.IsNullOrEmpty(dtStaggingData.Rows[iRow]["Column" + sourceIndx].ToString()))
                                    {
                                        if (targetColumnLength != 0)
                                        {
                                            if (dtStaggingData.Rows[iRow]["Column" + sourceIndx].ToString().Trim().Length > targetColumnLength)
                                            {
                                                //Add error column in dtStaggingData datatable with the help of last column id update last column with the error message
                                                errorColumn = errorColumn + "  " + targetColumnName + " length cannot be mor then " + targetColumnLength + " . ";
                                            }
                                        }
                                    }
                                    else
                                    {
                                        errorColumn = errorColumn + "  " + targetColumnName + " is mandatory.It cannot be empty. ";
                                    }
                                }

                                if (!string.IsNullOrEmpty(targetColumnValidation))
                                {
                                    if (dtStaggingData.Rows[iRow]["Column" + sourceIndx].ToString().Trim() == "")
                                    {
                                        errorColumn = errorColumn + targetColumnName + " is mandatory.It cannot be empty.";
                                    }
                                }
                                
                            }
                            //dtTemp.Rows.Add(dr);
                        }
                    }
                }
                else
                {
                    dtTemp = getStatementDataTable();
                    schemaString = "SELECT * FROM pro_statement where statement_unique_id = 0";
                }
                /*if (dtField_mapping_id != null && dtField_mapping_id.Rows.Count > 0)
                {
                    for (int iRow = 0; iRow < dtStaggingData.Rows.Count; iRow++)
                    {
                        DataRow dr = dtTemp.NewRow();
                        for (int i = 0; i < dtField_mapping_id.Rows.Count; i++)
                        {
                            int sourceIndx = Convert.ToInt32(dtField_mapping_id.Rows[i]["Source_Field_Index"]);
                            int targetIndx = Convert.ToInt32(dtField_mapping_id.Rows[i]["Target_Field_Index"]);
                            dr[targetIndx - 1] = dtStaggingData.Rows[iRow]["Column" + sourceIndx];

                        }
                        dtTemp.Rows.Add(dr);
                    }
                }*/

                SqlDataAdapter adpInvoiceStatement = new SqlDataAdapter(schemaString, connection);
                adpInvoiceStatement.UpdateBatchSize = 10;

               /* if (dtTemp != null)
                {
                    if (dtTemp.Rows.Count != 0)
                    {
                        // Update the DataSet with the Database
                        cmdBuilder = new SqlCommandBuilder(adpInvoiceStatement);
                        adpInvoiceStatement.Update(dtTemp);//Update database table with file record.
                        rowCount = dtTemp.Rows.Count;
                    }
                }*/

                if (rowCount > 0)
                {
                    string strQueryInvoice = "update pro_file_upload_details set file_status = 'UPLOD',row_count = '" + rowCount + "' where customer_id = '" + objUserVO.CustomerID + "' and file_id = '" + Convert.ToString(autoFileId) + "' and file_type = '" + fileType + "'";
                    cmd = new SqlCommand(strQueryInvoice, connection);
                    int retval = cmd.ExecuteNonQuery();
                }
                return rowCount;

            }
            catch (Exception ex)
            {
                return rowCount;
                throw ex;
            }
            finally
            {

            }
        }
    }
}
